from flask import Flask, render_template, session,request, jsonify, send_from_directory
import flask
import configparser
import mysql.connector
from werkzeug.utils import secure_filename
import os
import encryption
from datetime import datetime
app = Flask(__name__)


#mysql -u root -p
config = configparser.ConfigParser()
config.read('sql_config.ini')
sql_cnx = mysql.connector.connect(database=config["DATABASE"]["Database"],user=config["DATABASE"]["User"],
                              password=config["DATABASE"]["Password"], host=config["DATABASE"]["Host"],)
app.secret_key = config["FLASK_SESSION"]["Secret_Key"]
cursor = sql_cnx.cursor(buffered=True)



@app.route('/')
def index():
    #not flask.request.cookies.get("userid")
    if not flask.request.cookies.get("userid") or not session.get('userid'):
        return flask.redirect("/login")
    else:

      session["userid"] = int(flask.request.cookies.get("userid"))
    query = "SELECT identity, full_name FROM Users WHERE id=%s"
    values = (session['userid'],)
    cursor.execute(query, values)
    query_result = cursor.fetchone()
    kind = query_result[0]
    if  kind == -1:  # Admin
        input = request.args.get('search_input')
        if request.args.get('search_input') is None:
            cursor.execute("SELECT * FROM Messages where receiver_id=%s", (session['userid'],))
        elif input.isnumeric():
            cursor.execute("SELECT * FROM Messages WHERE sender_id=%s AND receiver_id=%s", (int(input), session['userid']))
        else:
            cursor.execute("SELECT * FROM Messages WHERE (sender_name LIKE %s OR content LIKE %s) AND receiver_id=%s ", ('%' + input + '%', '%' + input + '%', session['userid']))

        messages = cursor.fetchall()
        print(messages)
        return flask.render_template("admin_index.html", kind='admin', name=session['name'], messages=messages, )
    elif kind == 1:
            user_id = flask.request.cookies.get("userid")
            input = request.args.get('search_input')
            if request.args.get('search_input') is None:
                cursor.execute(
                    "SELECT Courses.* FROM Courses JOIN Students_Course_Enrollments ON Courses.course_id = Students_Course_Enrollments.course_id WHERE Students_Course_Enrollments.student_id = %s AND Students_Course_Enrollments.status = %s",
                    (user_id, "Success"))
            elif input.isnumeric():
                cursor.execute(
                    "SELECT Courses.* FROM Courses JOIN Students_Course_Enrollments ON Courses.course_id = Students_Course_Enrollments.course_id WHERE Students_Course_Enrollments.student_id = %s AND Students_Course_Enrollments.status = %s AND Students_Course_Enrollments.course_id = %s",
                    (user_id, "Success", int(input)))
            else:
                cursor.execute(
                    "SELECT Courses.* FROM Courses JOIN Students_Course_Enrollments ON Courses.course_id = Students_Course_Enrollments.course_id WHERE Students_Course_Enrollments.student_id = %s AND Students_Course_Enrollments.status = %s AND (Courses.course_description LIKE %s OR Courses.Instructor_name LIKE %s OR Courses.course_name LIKE %s )",
                    (user_id, "Success", '%' + input + '%', '%' + input + '%', '%' + input + '%'))

            courses = cursor.fetchall()



            return flask.render_template('stu_index.html', kind='student', name=session['name'], courses=courses, )

    elif kind == 0:
        user_id = flask.request.cookies.get("userid")
        input = request.args.get('search_input')
        if not request.args.get('search_input'):
            cursor.execute(
                "SELECT * FROM Courses WHERE instructor_id=%s", (session['userid'],))
        elif input.isnumeric():
            cursor.execute(
                "SELECT * FROM Courses WHERE instructor_id=%s AND course_id=%s", (int(input), session['userid']) )
        else:
            cursor.execute(
                "SELECT * FROM Courses WHERE instructor_id=%s AND (course_name LIKE %s OR course_description LIKE %s)",
                (user_id, '%' + input + '%', '%' + input + '%'))

        courses = cursor.fetchall()

        return flask.render_template('ins_index.html', kind='instructor', name=session['name'], courses=courses)

    else:
        return "Error"

@app.route('/register', methods=["GET", "POST"])
def register():
    return render_template('register.html')

@app.route('/login', methods=["GET", "POST"])
def login():
    if flask.request.method == 'GET':
        return flask.render_template("login.html")
    else:
        id = flask.request.form["userid"]
        password = flask.request.form["password"]
        password = encryption.MD5(password)
        cursor.execute("SELECT * FROM Users WHERE id=%s AND password=%s", (id, password))
        results = cursor.fetchone()
        if results == None:
            return flask.jsonify({'status': 'error', 'message': 'Incorrect User ID or Password, please try again.'})
            return flask.redirect("/login")

        else:
            session['userid'] = id
            session['name'] = results[1]
            session['kind'] = results[3]
            respponse = flask.make_response(flask.jsonify({'status': 'success', 'message': 'welcome, users'}))
            respponse.set_cookie('userid', str(id), max_age=50000)
            return respponse


@app.route('/logout')
def logout():
    respponse = flask.make_response(flask.redirect(flask.url_for('login')))
    respponse.delete_cookie('userid')
    session.clear()
    return respponse

@app.route('/message/delete', methods=['GET'])
def message_delete():
    if not flask.request.cookies.get("userid"):
        return flask.redirect("/login")
    try:
        id = flask.request.args['message_id']
        cursor.execute("DELETE FROM Messages WHERE message_id=%s", (id,))
        sql_cnx.commit()
        return flask.redirect("/")
    except Exception as e:
        sql_cnx.rollback()
        print(e)
        return flask.redirect("/")

@app.route('/mark-all-as-viewed', methods=['GET', 'POST'])
def mark_all_as_viewed():
    cursor.execute("UPDATE Messages SET viewed=1")
    sql_cnx.commit()
    return jsonify({'status': 'success'})

@app.route('/account')
def account_page():
    if not flask.request.cookies.get("userid") or session["kind"] != -1:
        return flask.redirect("/login")
    else:
        input = request.args.get('search_input')
        if request.args.get('search_input') is None:

            cursor.execute("SELECT * FROM Users")


        elif input.isnumeric():
            cursor.execute("SELECT * FROM Users WHERE id=%s", (int(input),))

        elif input.upper() in ("ADMINISTRATOR", "INSTRUCTOR", "STUDENT", "ADMIN", "STU", "INS"):
            user_types = ("ADMINISTRATOR", "INSTRUCTOR", "STUDENT", "ADMIN", "INS", "STU")
            for i, type in enumerate(user_types):
                if input.upper() == type:
                    input = (i % 3) - 1
                    break
            cursor.execute("SELECT * FROM Users WHERE identity = %s", (input,))

        else:
            cursor.execute("SELECT * FROM Users WHERE full_name LIKE %s", ('%' + input + '%',))

        users = cursor.fetchall()
        account_types = ["Administrator", "Instructor", "Student"]

        for i, user in enumerate(users):
            kind = user[3]
            print(account_types[kind + 1])
            user = (user[0], user[1], account_types[kind + 1], user[4])
            users[i]= user

        print(users)
        return flask.render_template('account_management.html', kind='admin', name=session['name'], users=users)


@app.route('/account/edit', methods=['GET', 'POST'])
def account_editing():
    if not flask.request.cookies.get("userid") or session["kind"] != -1:
        return flask.redirect("/login")
    if flask.request.method == 'GET':
        id = flask.request.args['user_id']
        cursor.execute("SELECT * FROM Users WHERE id=%s", (id,))
        result = cursor.fetchone()
        print(result)
        name = result[1]
        return flask.render_template("edit_window.html", Fullname=name,  user_id=id, mode="Edit Mode",)
    else:
        password_1 = flask.request.form["password"]
        password_2 = flask.request.form["confirm_password"]
        name = flask.request.form["Fullname"]
        if password_1 == password_2:
            kind = int(flask.request.form["User_Type"])
            ID = flask.request.form["ID"]

        else:
            return jsonify({'status': 'error', 'message': 'Please ensure your passwords are the same.'})
    try:
            password_1 = encryption.MD5(password_1).upper()
            cursor.execute("UPDATE users SET full_name= %s, password=%s, identity=%s WHERE id=%s", (name, password_1, kind, ID))
            sql_cnx.commit()
            return jsonify({'status': 'success', 'message': 'Scucess!'})
    except Exception as e:
            sql_cnx.rollback()
            return jsonify({'status': 'error', 'message': f'error:{e} '})


@app.route('/account/view', methods=['GET', 'POST'])
def account_view():
    if not flask.request.cookies.get("userid") or session["kind"] != -1:
        return flask.redirect("/login")
    else:
        id = flask.request.args['user_id']
        cursor.execute("SELECT * FROM Users WHERE id=%s", (id,))
        result = cursor.fetchone()
        print(result)
        name = result[1]
        password = result[2]
        account_types = ["Administrator", "Instructor", "Student"]
        user_type=account_types[result[3] + 1]
        return flask.render_template("edit_window.html",  Fullname=name, user_id=id, mode="View Mode", kind=user_type, title="Account View")


@app.route('/account/create', methods=['GET', 'POST'])
def account_creating():
    if not flask.request.cookies.get("userid") or session["kind"] != -1:
        return flask.redirect("/login")
    if flask.request.method == 'GET':
        return flask.render_template("edit_window.html", mode="Create Mode")
    else:
        password_1 = flask.request.form["password"]
        password_2 = flask.request.form["confirm_password"]
        name = flask.request.form["Fullname"]
        if password_1 == password_2:
            kind = int(flask.request.form["User_Type"])
            ID = flask.request.form["ID"]
            password_1 = encryption.MD5(password_1)

        else:
            return jsonify({'status': 'error', 'message': 'Please ensure your passwords are the same.'})
    try:
            cursor.execute("INSERT INTO users (id, full_name, password, identity) VALUES (%s, %s, %s, %s)", (ID, name, password_1, kind))
            sql_cnx.commit()
            return jsonify({'status': 'success', 'message': 'Success!'})
    except Exception as e:
            sql_cnx.rollback()
            return jsonify({'status': 'error', 'message': f'error:{e} '})





@app.route('/message/view', methods=['GET', 'POST'])
def view_message():
    if not flask.request.cookies.get("userid"):
        return flask.redirect("/login")
    else:
        if flask.request.method == 'GET':
            id = flask.request.args['message_id']
            cursor.execute("SELECT * FROM Messages WHERE message_id=%s", (id,))
            information = cursor.fetchone()
            print(information)
            return render_template('window.html', edit=False, name=session['name'], information=information, mode="message", title="Message View")
        elif flask.request.method == 'POST':
            viewed = flask.request.form["viewed"]
            id = flask.request.form['message_id']
            cursor.execute("UPDATE Messages SET viewed=%s WHERE message_id=%s", (viewed, id))
            sql_cnx.commit()
            print(111)
            return jsonify({'status': 'success'})



@app.route('/account/delete', methods=['GET'])
def account_delete():
    if not flask.request.cookies.get("userid") or session["kind"] != -1:
        return flask.redirect("/login")
    try:
        id = flask.request.args['user_id']
        cursor.execute("DELETE FROM Users WHERE ID=%s", (id,))
        sql_cnx.commit()
        return flask.redirect("/account")
    except Exception as e:
        sql_cnx.rollback()
        print(e)
        return flask.redirect("/account")


@app.route('/course')
def course_page():
    if not flask.request.cookies.get("userid") or session["kind"] != -1:
        return flask.redirect("/login")
    else:
        input = request.args.get('search_input')
        if request.args.get('search_input') is None:
            cursor.execute("SELECT * FROM Courses")

        elif input.isnumeric():
            cursor.execute("SELECT * FROM Courses WHERE course_id=%s", (int(input),))
        else:
            cursor.execute("SELECT * FROM Courses WHERE (course_name LIKE %s OR course_description LIKE %s OR instructor_name LIKE %s)", ('%' + input + '%', '%' + input + '%', '%' + input + '%',))

        courses = cursor.fetchall()

        return flask.render_template('course_management.html', kind='admin', name=session['name'], courses=courses, )

@app.route('/course/view', methods=['GET'])
def view_course():
    if not flask.request.cookies.get("userid"):
        return flask.redirect("/login")
    else:
        if flask.request.method == 'GET':
            id = flask.request.args['course_id']
            cursor.execute("SELECT * FROM Courses WHERE course_id=%s", (id,))
            courses = cursor.fetchone()
            print(courses)

            cursor.execute("SELECT student_id FROM Students_Course_Enrollments WHERE course_id=%s", (id,))
            students_ids = cursor.fetchall()

            if students_ids:
                students_ids = [i[0] for i in students_ids]
                format_strings = ','.join(['%s'] * len(students_ids))

                cursor.execute(f"SELECT full_name, id FROM Users WHERE id IN ({format_strings})", tuple(students_ids))
                information = cursor.fetchall()
            else:
                information = []
            print(information)
            return render_template('window.html', edit=False, name=session['name'], courses=courses, mode="course", title="Course View",informations=information)

@app.route('/course/delete', methods=['GET'])
def course_delete():
    if not flask.request.cookies.get("userid") or session["kind"] != -1:
        return flask.redirect("/login")
    try:
        id = flask.request.args['course_id']
        cursor.execute("DELETE FROM Courses WHERE course_id=%s", (id,))
        sql_cnx.commit()
        return flask.redirect("/course")
    except Exception as e:
        sql_cnx.rollback()
        print(e)
        return flask.redirect("/course")


@app.route('/course/edit', methods=['GET', 'POST'])
def course_editing():
    if not flask.request.cookies.get("userid") or session["kind"] != -1:
        return flask.redirect("/login")
    if flask.request.method == 'GET':
        id = flask.request.args['course_id']
        cursor.execute("SELECT * FROM Courses WHERE course_id=%s", (id,))
        results = cursor.fetchone()


        cursor.execute("SELECT student_id FROM Students_Course_Enrollments WHERE course_id=%s", (id,))
        students_ids = cursor.fetchall()

        if students_ids:
            students_ids = [i[0] for i in students_ids]
            format_strings = ','.join(['%s'] * len(students_ids))

            cursor.execute(f"SELECT full_name, id FROM Users WHERE id IN ({format_strings})", tuple(students_ids))
            information = cursor.fetchall()
        else:
            information = []
        return flask.render_template("edit_window.html", results=results, user_id=id, students=information, mode="Edit Mode Course",)
    else:
        course_name= flask.request.form.get('course_name')
        description = flask.request.form.get("description")
        name = flask.request.form.get("instructor_name")
        id = flask.request.form.get('course_id')

        cursor.execute(
            "SELECT full_name FROM Users WHERE identity=%s",(0,))
        instructors = cursor.fetchall()
        if (name, ) not in instructors:
            return jsonify({'status': 'error', 'message': 'There is no instructor matches with your input'})

        students = request.form.getlist('student')
        student_ids = []
        cursor.execute(
            "SELECT full_name, id FROM Users WHERE identity=%s", (1,))
        query_students = cursor.fetchall()
        query_students = [item for sublist in query_students for item in sublist]
        for student in students:
            if student.isnumeric():
                student_ids.append(int(student))
            else:
                for i, query_student in enumerate(query_students):
                    if student == query_student:
                        student_ids.append(query_students[i + 1])
                        break
        params = [(id, student_id, "Success") for student_id in student_ids]
        cursor.executemany(
            "INSERT INTO Students_course_Enrollments (course_id, student_id, status) VALUES (%s, %s, %s)", params)

    try:
            cursor.execute("UPDATE Courses SET course_name= %s, instructor_name=%s, course_description=%s WHERE course_id=%s", (course_name, name, description, id))
            sql_cnx.commit()
            return jsonify({'status': 'success', 'message': 'Success!'})
    except Exception as e:
            sql_cnx.rollback()
            return jsonify({'status': 'error', 'message': f'error:{e} '})


@app.route('/course/create', methods=['GET', 'POST'])
def course_creating():
    if not flask.request.cookies.get("userid") or session["kind"] != -1:
        return flask.redirect("/login")
    if flask.request.method == 'GET':
        return flask.render_template("edit_window.html", mode="Create Mode Course")
    else:
        course_name = flask.request.form.get('course_name1')
        description = flask.request.form.get("description1")
        name = flask.request.form.get("instructor_name1")
        ins_id = flask.request.form.get("instructor_id")
        id = flask.request.form.get('course_id1')
        students = request.form.getlist('student')
        cursor.execute(
            "SELECT full_name, id FROM Users WHERE identity=%s", (0,))
        instructors = cursor.fetchall()
        instructors = [item for sublist in instructors for item in sublist]
        print(instructors)
        if name not in instructors:
            return jsonify({'status': 'error', 'message': 'There is no instructor matches with your input'})

        cursor.execute(
            "SELECT full_name, id FROM Users WHERE identity=%s", (1,))
        query_students = cursor.fetchall()
        query_students = [item for sublist in query_students for item in sublist]
        print(query_students)
        for student in students:
            if student.isnumeric():
                student = int(student)
            if student not in query_students:
                return jsonify({'status': 'error', 'message': 'The student ' + str(student) + ' is not exist in the system.'})

    student_ids= []

    for student in students:
        if student.isnumeric():
            student_ids.append(int(student))
        else:
            for i, query_student in enumerate(query_students):
                if student == query_student:
                    student_ids.append(query_students[i+1])
                    break

    params = [(id, student_id, "Success") for student_id in student_ids]
    print(params)
    cursor.executemany(
        "INSERT INTO Students_course_Enrollments (course_id, student_id, status) VALUES (%s, %s, %s)", params)

    try:
            cursor.execute("INSERT INTO courses (course_id, course_name, instructor_name, instructor_id, course_description) VALUES (%s, %s, %s, %s, %s)", (id, course_name, name, ins_id, description))
            sql_cnx.commit()
            return jsonify({'status': 'success', 'message': 'Success!'})
    except Exception as e:
            sql_cnx.rollback()
            return jsonify({'status': 'error', 'message': f'error:{e} '})


@app.route('/course/delete_student', methods=['GET'])
def delete_student():
    if not flask.request.cookies.get("userid") or session["kind"] != -1:
        return flask.redirect("/login")
    try:
        course_id = flask.request.args['course_id']
        student_id = flask.request.args['student_id']
        cursor.execute("DELETE FROM Students_Course_Enrollments WHERE course_id=%s AND student_id=%s", (course_id, student_id))
        sql_cnx.commit()
        return flask.redirect("/course/edit?course_id=" + course_id)
    except Exception as e:
        sql_cnx.rollback()
        print(e)
        return flask.redirect("/course/edit?course_id=" + course_id)

@app.route('/course/request')
def request_course():
    if not flask.request.cookies.get("userid") or session["kind"] != -1:
        return flask.redirect("/login")
    else:
        input = request.args.get('search_input')
        if request.args.get('search_input') is None:
            cursor.execute("SELECT * FROM Students_Course_Enrollments where status=%s", ("Applying",))

        elif input.isnumeric():
            cursor.execute("SELECT * FROM Students_Course_Enrollments where (student_id = %s OR enrollment_id = %s OR course_id = %s) AND status=%s", (int(input), int(input), int(input), "Applying"))

        requests = cursor.fetchall()


        course_ids = [request[1] for request in requests]
        print(course_ids)
        course_names = []
        for course_id in course_ids:
            cursor.execute("SELECT course_name FROM Courses where course_id=%s", (course_id,))
            course_name = cursor.fetchone()
            print(course_name)
            if course_name is None:
                course_ids.remove(course_id)
                continue
            course_names.append(course_name[0])


        new_requests = []
        for i, req in enumerate(requests):
            new_req = list(req)  # 将元组转换为列表，以便我们可以修改它
            new_req[1] = course_names[i]  # 替换course_id为course_name
            new_requests.append(tuple(new_req))  # 将列表转换回元组，并添加到新的requests列表中

        print(new_requests)

        return flask.render_template('request_management.html', kind='admin', name=session['name'], requests=new_requests, course_names=course_names)



@app.route('/course/request/accept', methods=['GET', 'POST'])
def accept_request():
    if not flask.request.cookies.get("userid") or session["kind"] != -1:
        return flask.redirect("/login")
    try:
        id = flask.request.args['enrollment_id']
        cursor.execute("UPDATE Students_Course_Enrollments SET status=%s WHERE enrollment_id=%s", ("Success", id,))
        sql_cnx.commit()
        return flask.redirect("/course/request")
    except Exception as e:
        sql_cnx.rollback()
        print(e)
        return flask.redirect("/course/request")

@app.route('/course/request/reject', methods=['GET', 'POST'])
def reject_request():
    if not flask.request.cookies.get("userid") or session["kind"] != -1:
        return flask.redirect("/login")
    try:
        id = flask.request.args['enrollment_id']
        cursor.execute("UPDATE Students_Course_Enrollments SET status=%s WHERE enrollment_id=%s", ("Reject", id,))
        sql_cnx.commit()
        return flask.redirect("/course/request")
    except Exception as e:
        sql_cnx.rollback()
        print(e)
        return flask.redirect("/course/request")



@app.route('/lecture', methods=['GET', 'POST'])
def lecture_page():
    if not session.get('userid'):
        return flask.redirect("/login")
    if flask.request.method == 'GET':
        # 显示所有讲座信息
        input = request.args.get('search_input')
        if request.args.get('search_input') is None:
            cursor.execute("SELECT Lectures.*, Courses.course_name FROM Lectures JOIN Courses ON Lectures.course_id = Courses.course_id")
            lectures = cursor.fetchall()

        elif input.isnumeric():
            cursor.execute("SELECT Lectures.*, Courses.course_name FROM Lectures JOIN Courses ON Lectures.course_id = Courses.course_id WHERE Lectures.course_id=%s OR Lectures.Lecture_id=%s", (int(input),int(input),))
            lectures = cursor.fetchall()

        else:
            cursor.execute("SELECT Lectures.*, Courses.course_name FROM Lectures JOIN Courses ON Lectures.course_id = Courses.course_id  WHERE (Lectures.lecture_name LIKE %s OR Lectures.lecture_description LIKE %s OR Lectures.instructor_name LIKE %s OR Courses.course_name LIKE %s)", ('%' + input + '%', '%' + input + '%', '%' + input + '%', '%' + input + '%'))
            lectures = cursor.fetchall()

        print(lectures)
        return render_template('lecture_management.html', lectures=lectures, kind='admin', name=session['name'])

@app.route('/lecture/delete', methods=['GET'])
def lecture_delete():
    if not flask.request.cookies.get("userid") or session["kind"] != -1:
        return flask.redirect("/login")
    try:
        id = flask.request.args['lecture_id']
        cursor.execute("UPDATE FROM Lectures WHERE lecture_id=%s", (id,))
        sql_cnx.commit()
        return flask.redirect("/lecture")
    except Exception as e:
        sql_cnx.rollback()
        print(e)
        return flask.redirect("/lecture")



@app.route('/lecture/create', methods=['GET', 'POST'])
def lecture_creating():
    if not flask.request.cookies.get("userid") or (session["kind"] != -1 and session["kind"] != 0):
        return flask.redirect("/login")
    if flask.request.method == 'GET':
        return flask.render_template("edit_window.html", mode="Create Mode Lecture")
    else:
        lecture_name = flask.request.form.get('lecture_name')
        description = flask.request.form.get("description")
        name = flask.request.form.get("instructor_name")
        lecture_id = flask.request.form.get('lecture_id')
        course_name = flask.request.form.get('course_name')
        if 'uploaded_file' not in request.files:
            pass
        else:
            file = request.files['uploaded_file']
            if file.filename != '':
                filename = secure_filename(file.filename)
            else:
                filename = "default"

            file_path = os.path.join('static/files', filename)
            file.save(file_path)

        cursor.execute(
            "SELECT full_name FROM Users WHERE identity=%s", (0,))
        instructors = cursor.fetchall()
        if (name,) not in instructors:
            return jsonify({'status': 'error', 'message': 'There is no instructor matches with your input'})
        try:
            cursor.execute(
            "SELECT course_id FROM Courses WHERE course_name=%s", (course_name,))
            course_id = cursor.fetchall()
            course_id = course_id[0][0]
        except Exception as e:
            sql_cnx.rollback()
            return jsonify({'status': 'match_error', 'message': 'There is no course matches with your input'})
    try:
            cursor.execute("INSERT INTO lectures (lecture_id, course_id, lecture_name, instructor_name, lecture_description, file_path) VALUES (%s, %s, %s, %s, %s, %s)", (lecture_id, course_id, lecture_name, name, description, file_path))
            sql_cnx.commit()
            return jsonify({'status': 'success', 'message': 'Success!'})
    except Exception as e:
            sql_cnx.rollback()
            return jsonify({'status': 'error', 'message': f'error:{e} '})


@app.route('/lecture/view', methods=['GET'])
def view_lecture():
    if not flask.request.cookies.get("userid") or session["kind"] != -1:
        return flask.redirect("/login")
    else:
        if flask.request.method == 'GET':
            id = flask.request.args['lecture_id']
            cursor.execute("SELECT * FROM Lectures WHERE lecture_id=%s", (id,))
            lectures = cursor.fetchone()
            print(lectures)
            return render_template('window.html', edit=False, name=session['name'], lectures=lectures, mode="lecture", title="Lecture View",)




@app.route('/lecture/edit', methods=['GET', 'POST'])
def lecture_editing():
    if not flask.request.cookies.get("userid") or session["kind"] != -1:
        return flask.redirect("/login")
    if flask.request.method == 'GET':
        id = flask.request.args['lecture_id']
        cursor.execute("SELECT * FROM Lectures WHERE lecture_id=%s", (id,))
        results = cursor.fetchone()

        print(results)
        return flask.render_template("edit_window.html", results=results, user_id=id, mode="Edit Mode Lecture",)
    else:
        course_name= flask.request.form.get('lecture_name')
        description = flask.request.form.get("description")
        name = flask.request.form.get("instructor_name")
        id = flask.request.form.get('lecture_id')


        uploaded_file = request.files['uploaded_file']

        if not uploaded_file.filename == '':
            cursor.execute(
                "SELECT file_path FROM Lectures WHERE lecture_id=%s", (id,))
            old_file_path = cursor.fetchone()[0]
            if old_file_path != None:
                os.remove(old_file_path)

            file = request.files['uploaded_file']
            if file.filename != '':
                filename = secure_filename(file.filename)
            else:
                filename = "default"
            file_path = os.path.join('static/files', filename)
            file.save(file_path)
            cursor.execute(
                "UPDATE Lectures SET file_path=%s WHERE lecture_id=%s", (file_path, id))

        cursor.execute(
            "SELECT full_name FROM Users WHERE identity=%s",(0,))
        instructors = cursor.fetchall()
        if (name, ) not in instructors:
            return jsonify({'status': 'error', 'message': 'There is no instructor matches with your input'})


    try:
            cursor.execute("UPDATE Lectures SET lecture_name= %s, instructor_name=%s, lecture_description=%s WHERE lecture_id=%s", (course_name, name, description, id))
            sql_cnx.commit()
            return jsonify({'status': 'success', 'message': 'Success!'})
    except Exception as e:
            sql_cnx.rollback()
            return jsonify({'status': 'error', 'message': f'error:{e} '})

@app.route('/student')
def stu_index():
    if not flask.request.cookies.get("userid") or session["kind"] != 1:
        return flask.redirect("/login")
    else:
        student_id = flask.request.cookies.get("userid")
        input = request.args.get('search_input')
        if request.args.get('search_input') is None:
            cursor.execute("SELECT Courses.* FROM Courses JOIN Students_Course_Enrollments ON Courses.course_id = Students_Course_Enrollments.course_id AND Students_Course_Enrollments.student_id = %s AND Students_Course_Enrollments.status = %s ", (student_id, "Success"))

        elif input.isnumeric():
            cursor.execute("SELECT Courses.* FROM Courses JOIN Students_Course_Enrollments ON Courses.course_id = Students_Course_Enrollments.course_id AND Students_Course_Enrollments.student_id = %s AND Students_Course_Enrollments.status = %s WHERE Courses.course_id=%s", (student_id, "Success", int(input),))
        else:
            cursor.execute("SELECT Courses.* FROM Courses JOIN Students_Course_Enrollments ON Courses.course_id = Students_Course_Enrollments.course_id AND Students_Course_Enrollments.student_id = %s AND Students_Course_Enrollments.status = %s WHERE (Courses.course_name LIKE %s OR Courses.course_description LIKE %s OR Courses.instructor_name LIKE %s)", (student_id, "Success", '%' + input + '%', '%' + input + '%', '%' + input + '%'))

        courses = cursor.fetchall()

        return flask.render_template('stu_index.html', kind='student', name=session['name'], courses=courses, )


@app.route('/enroll')
def enrollment():
    if not flask.request.cookies.get("userid") or session["kind"] != 1:
        return flask.redirect("/login")
    else:
        user_id = flask.request.cookies.get("userid")
        input = request.args.get('search_input')
        if not request.args.get('search_input'):
            cursor.execute("SELECT Courses.* FROM Courses LEFT JOIN Students_Course_Enrollments ON Courses.course_id = Students_Course_Enrollments.course_id AND Students_Course_Enrollments.student_id = %s AND (Students_Course_Enrollments.status = %s OR Students_Course_Enrollments.status = %s) WHERE Students_Course_Enrollments.enrollment_id IS NULL; ", (user_id, "Success", "Applying"))
        elif input.isnumeric():
            cursor.execute("SELECT Courses.* FROM Courses LEFT JOIN Students_Course_Enrollments ON Courses.course_id = Students_Course_Enrollments.course_id AND Students_Course_Enrollments.student_id = %s AND Students_Course_Enrollments.status = %s AND Students_Course_Enrollments.status = %s WHERE Students_Course_Enrollments.enrollment_id IS NULL AND Courses.course_id=%s;", (user_id, "Success", "Applying", int(input)))
        else:
            cursor.execute("SELECT Courses.* FROM Courses LEFT JOIN Students_Course_Enrollments ON Courses.course_id = Students_Course_Enrollments.course_id AND Students_Course_Enrollments.student_id = %s AND (Students_Course_Enrollments.status = %s OR Students_Course_Enrollments.status = %s)  WHERE Students_Course_Enrollments.enrollment_id IS NULL AND (Courses.course_name LIKE %s OR Courses.course_description LIKE %s OR Courses.instructor_name LIKE %s)",
                (user_id, "Success", "Applying", '%' + input + '%', '%' + input + '%', '%' + input + '%'))

        courses = cursor.fetchall()

        return flask.render_template('course_enrollment.html', kind='student', name=session['name'], courses=courses, )


@app.route('/enroll/apply', methods=['GET', 'POST'])
def enroll_request():
    if not flask.request.cookies.get("userid") or session["kind"] != 1:
        return flask.redirect("/login")
    try:
        course_id = flask.request.args.get("course_id")
        student_id = flask.request.cookies.get("userid")
        print(course_id)

        cursor.execute("INSERT INTO Students_Course_Enrollments (course_id, student_id, status) VALUES(%s, %s, %s)", (course_id, student_id, "Applying",))

        sql_cnx.commit()
        return flask.redirect("/enroll")
    except Exception as e:
        sql_cnx.rollback()
        print(e)
        return flask.redirect("/enroll")

@app.route('/message/send', methods=['GET', 'POST'])
def admin_enroll_request():
    if not flask.request.cookies.get("userid"):
        return flask.redirect("/login")

    if flask.request.method == 'GET':
        return flask.render_template("window.html", mode="send")
    try:
        sender_id = flask.request.cookies.get("userid")
        receiver_id = flask.request.form.get("id")
        content = flask.request.form.get("content")
        send_time = datetime.now().strftime('%Y-%m-%d, %H:%M:%S')
        cursor.execute("SELECT full_name from Users WHERE id = %s", (sender_id,))
        sender_name = cursor.fetchall()[0][0]
        cursor.execute("INSERT INTO Messages (sender_id, receiver_id, content, viewed, send_time, sender_name) VALUES (%s, %s, %s, %s, %s, %s)", (sender_id, receiver_id, content, 0, send_time, sender_name))

        sql_cnx.commit()
        return flask.redirect("/")
    except Exception as e:
        sql_cnx.rollback()
        print(e)
        return flask.redirect("/")




@app.route('/message', methods=['GET', 'POST'])
def message_page():
    # not flask.request.cookies.get("userid")
    if not flask.request.cookies.get("userid") or not session.get('userid'):
        return flask.redirect("/login")
    else:

        input = request.args.get('search_input')
        if request.args.get('search_input') is None:
            cursor.execute("SELECT * FROM Messages where receiver_id=%s", (session['userid'],))
        elif input.isnumeric():
            cursor.execute("SELECT * FROM Messages WHERE sender_id=%s AND receiver_id=%s",
                           (int(input), session['userid']))
        else:
            cursor.execute("SELECT * FROM Messages WHERE (sender_name LIKE %s OR content LIKE %s) AND receiver_id=%s ",
                           ('%' + input + '%', '%' + input + '%', session['userid']))

        messages = cursor.fetchall()
        print(messages)
        return flask.render_template("stu_message.html", kind='student', name=session['name'], messages=messages, )



@app.route('/instructor/message', methods=['GET', 'POST'])
def ins_message_page():
    # not flask.request.cookies.get("userid")
    if not flask.request.cookies.get("userid") or not session.get('userid'):
        return flask.redirect("/login")
    else:

        input = request.args.get('search_input')
        if request.args.get('search_input') is None:
            cursor.execute("SELECT * FROM Messages where receiver_id=%s", (session['userid'],))
        elif input.isnumeric():
            cursor.execute("SELECT * FROM Messages WHERE sender_id=%s AND receiver_id=%s",
                           (int(input), session['userid']))
        else:
            cursor.execute("SELECT * FROM Messages WHERE (sender_name LIKE %s OR content LIKE %s) AND receiver_id=%s ",
                           ('%' + input + '%', '%' + input + '%', session['userid']))
        messages = cursor.fetchall()
        return flask.render_template("ins_message.html", kind='Instructor', name=session['name'], messages=messages, )


@app.route('/course/lectures', methods=['GET', 'POST'])
def stu_lecture():
    if not session.get('userid'):
        return flask.redirect("/login")
    if flask.request.method == 'GET':

        course_id = flask.request.args.get('course_id')
        # 显示所有讲座信息
        input = request.args.get('search_input')
        if request.args.get('search_input') is None:
            cursor.execute("SELECT Lectures.*, Courses.course_name FROM Lectures JOIN Courses ON Lectures.course_id = Courses.course_id WHERE Courses.course_id=%s", (course_id,))
            lectures = cursor.fetchall()

        elif input.isnumeric():
            cursor.execute("SELECT Lectures.*, Courses.course_name FROM Lectures JOIN Courses ON Lectures.course_id = Courses.course_id WHERE (Lectures.course_id=%s OR Lectures.Lecture_id=%s) AND Courses.course_id=%s", (int(input), int(input), course_id))
            lectures = cursor.fetchall()

        else:
            cursor.execute("SELECT Lectures.*, Courses.course_name FROM Lectures JOIN Courses ON Lectures.course_id = Courses.course_id  WHERE (Lectures.lecture_name LIKE %s OR Lectures.lecture_description LIKE %s OR Lectures.instructor_name LIKE %s OR Courses.course_name LIKE %s) AND Courses.course_id=%s", ('%' + input + '%', '%' + input + '%', '%' + input + '%', '%' + input + '%', course_id))
            lectures = cursor.fetchall()

        print(lectures)
        return render_template('stu_lectures.html', lectures=lectures, kind='student', name=session['name'])



@app.route('/course/lectures_ins', methods=['GET', 'POST'])
def ins_lecture():
    if not session.get('userid'):
        return flask.redirect("/login")
    if flask.request.method == 'GET':

        course_id = flask.request.args.get('course_id')
        # 显示所有讲座信息
        input = request.args.get('search_input')
        if request.args.get('search_input') is None:
            cursor.execute("SELECT Lectures.*, Courses.course_name FROM Lectures JOIN Courses ON Lectures.course_id = Courses.course_id WHERE Courses.course_id=%s", (course_id,))
            lectures = cursor.fetchall()

        elif input.isnumeric():
            cursor.execute("SELECT Lectures.*, Courses.course_name FROM Lectures JOIN Courses ON Lectures.course_id = Courses.course_id WHERE (Lectures.course_id=%s OR Lectures.Lecture_id=%s) AND Courses.course_id=%s", (int(input), int(input), course_id))
            lectures = cursor.fetchall()

        else:
            cursor.execute("SELECT Lectures.*, Courses.course_name FROM Lectures JOIN Courses ON Lectures.course_id = Courses.course_id  WHERE (Lectures.lecture_name LIKE %s OR Lectures.lecture_description LIKE %s OR Lectures.instructor_name LIKE %s OR Courses.course_name LIKE %s) AND Courses.course_id=%s", ('%' + input + '%', '%' + input + '%', '%' + input + '%', '%' + input + '%', course_id))
            lectures = cursor.fetchall()

        print(lectures)
        return render_template('ins_lectures.html', lectures=lectures, kind='Instructor', name=session['name'])

@app.route('/assignment/student', methods=['GET', 'POST'])
def assignment_student():
    if not flask.request.cookies.get("userid") or session["kind"] != 1:
        return flask.redirect("/login")
    else:
        input = request.args.get('search_input')
        if not request.args.get('search_input'):
            cursor.execute("SELECT assignments.* FROM assignments JOIN Students_Course_Enrollments ON assignments.course_id = Students_Course_Enrollments.course_id where assignments.assignment_id NOT IN (SELECT Submissions.assignment_id FROM Submissions WHERE Submissions.student_id = Students_Course_Enrollments.student_id) AND Students_Course_Enrollments.student_id=%s AND Students_Course_Enrollments.status=%s ", (session['userid'], "Success"))
        elif input.isnumeric():
            cursor.execute(
                "SELECT assignments.* FROM assignments JOIN Students_Course_Enrollments ON assignments.course_id = Students_Course_Enrollments.course_id where assignments.assignment_id NOT IN (SELECT Submissions.assignment_id FROM Submissions WHERE Submissions.student_id = Students_Course_Enrollments.student_id) AND Students_Course_Enrollments.student_id=%s AND Students_Course_Enrollments.status=%s AND (assignments.assignment_id=%s)",
                (session['userid'], "Success", int(input)))

        else:
            cursor.execute(
                "SELECT assignments.* FROM assignments JOIN Students_Course_Enrollments ON assignments.course_id = Students_Course_Enrollments.course_id where assignments.assignment_id NOT IN (SELECT Submissions.assignment_id FROM Submissions WHERE Submissions.student_id = Students_Course_Enrollments.student_id) AND Students_Course_Enrollments.student_id=%s AND Students_Course_Enrollments.status=%s AND (assignments.assignment_name LIKE %s OR assignments.course_name LIKE %s OR assignments.description LIKE %s)",
                (session['userid'], "Success", '%' + input + '%', '%' + input + '%', '%' + input + '%',))

        assignments = cursor.fetchall()
        print(assignments)
        return flask.render_template("stu_assignment.html", kind='student', name=session['name'], assignments=assignments, )


@app.route('/assignment/student/submitted', methods=['GET', 'POST'])
def assignment_student_submitted():
    if not flask.request.cookies.get("userid") or session["kind"] != 1:
        return flask.redirect("/login")
    else:
        input = request.args.get('search_input')
        if not request.args.get('search_input'):
            cursor.execute("SELECT assignments.* , Submissions.Score, Submissions.submission_id FROM assignments JOIN Students_Course_Enrollments ON assignments.course_id = Students_Course_Enrollments.course_id  LEFT JOIN Submissions ON assignments.assignment_id = Submissions.assignment_id AND Submissions.student_id = Students_Course_Enrollments.student_id where assignments.assignment_id IN (SELECT Submissions.assignment_id FROM Submissions WHERE Submissions.student_id = Students_Course_Enrollments.student_id) AND Students_Course_Enrollments.student_id=%s AND Students_Course_Enrollments.status=%s ", (session['userid'], "Success"))
        elif input.isnumeric():
            cursor.execute(
                "SELECT assignments.* FROM assignments JOIN Students_Course_Enrollments ON assignments.course_id = Students_Course_Enrollments.course_id where assignments.assignment_id IN (SELECT Submissions.assignment_id FROM Submissions WHERE Submissions.student_id = Students_Course_Enrollments.student_id) AND Students_Course_Enrollments.student_id=%s AND Students_Course_Enrollments.status=%s AND (assignments.assignment_id=%s)",
                (session['userid'], "Success", int(input)))

        else:
            cursor.execute(
                "SELECT assignments.* FROM assignments JOIN Students_Course_Enrollments ON assignments.course_id = Students_Course_Enrollments.course_id where assignments.assignment_id IN (SELECT Submissions.assignment_id FROM Submissions WHERE Submissions.student_id = Students_Course_Enrollments.student_id) AND Students_Course_Enrollments.student_id=%s AND Students_Course_Enrollments.status=%s AND (assignments.assignment_name LIKE %s OR assignments.course_name LIKE %s OR assignments.description LIKE %s)",
                (session['userid'], "Success", '%' + input + '%', '%' + input + '%', '%' + input + '%',))

        assignments = cursor.fetchall()
        print(assignments)
        return flask.render_template("stu_assignment_submitted.html", kind='student', name=session['name'], assignments=assignments, )

@app.route('/assignment/create', methods=['GET', 'POST'])
def assignment_creating():
    if not flask.request.cookies.get("userid") or (session["kind"] != -1 and session["kind"] != 0):
        return flask.redirect("/login")
    if flask.request.method == 'GET':
        course_id = request.args.get('course_id')
        cursor.execute(
            "SELECT course_name FROM Courses WHERE course_id=%s", (course_id,))
        course_name = cursor.fetchall()
        course_name = course_name[0][0]
        return flask.render_template("edit_window.html", mode="Create Mode Assignment", course_name=course_name, course_id=course_id)
    else:
        instructor_id = flask.request.cookies.get("userid")
        assignment_id = flask.request.form.get("assignment_id")
        assignment_name = flask.request.form.get('assignment_name')
        description = flask.request.form.get("description")
        course_id = flask.request.form.get('course_id')
        course_name = flask.request.form.get('course_name')
    try:
            cursor.execute("INSERT INTO Assignments (assignment_id, assignment_name, description, course_id, course_name, instructor_id) VALUES (%s, %s, %s, %s, %s, %s)", (assignment_id, assignment_name, description, course_id, course_name, instructor_id))
            sql_cnx.commit()
            return jsonify({'status': 'success', 'message': 'Success!'})
    except Exception as e:
            sql_cnx.rollback()
            return jsonify({'status': 'error', 'message': f'error:{e} '})




@app.route('/assignment/page', methods=['GET', 'POST'])
def assignment_page():
        user_id = flask.request.cookies.get("userid")
        input = request.args.get('search_input')
        if not request.args.get('search_input'):
            cursor.execute(
                "SELECT * FROM Assignments WHERE instructor_id=%s", (session['userid'],))
        elif input.isnumeric():
            cursor.execute(
                "SELECT * FROM Assignments WHERE instructor_id=%s AND (course_id=%s OR assignment_id=%s)", (session['userid'], int(input), int(input)))
        else:
            cursor.execute(
                "SELECT * FROM Assignments WHERE instructor_id=%s AND (assignment_name LIKE %s OR description LIKE %s)",
                (user_id, '%' + input + '%', '%' + input + '%'))

        assignments = cursor.fetchall()
        print(assignments)
        return flask.render_template('assignment_page.html', kind='instructor', name=session['name'], assignments=assignments)


@app.route('/assignment/page/view', methods=['GET',])
def view_assignment():
    if not flask.request.cookies.get("userid") or (session["kind"] != -1 and session["kind"] != 0):
        return flask.redirect("/login")
    else:
        if flask.request.method == 'GET':
            id = flask.request.args['assignment_id']
            cursor.execute("SELECT * FROM Assignments WHERE assignment_id=%s", (id,))
            information = cursor.fetchone()
            print(information)
            return render_template('window.html', edit=False, name=session['name'], information=information, mode="assignment", title="Assignment View")


@app.route('/assignment/student/submit', methods=['GET', 'POST'])
def assignment_submit():
    if not flask.request.cookies.get("userid") or session["kind"] != 1 :
        return flask.redirect("/login")
    assignment_id = flask.request.args.get('assignment_id')
    session["assignment_id"] = assignment_id
    if flask.request.method == 'GET':
        cursor.execute("SELECT assignment_name, description FROM Assignments WHERE assignment_id=%s",
                       (assignment_id,))
        assignment_info = cursor.fetchall()
        print(assignment_info[0][1])
        return flask.render_template("edit_window.html", mode="Create Mode Submission", assignment_id=assignment_id, assignment_name=assignment_info[0][0], description=assignment_info[0][1])
    else:
        if 'uploaded_file' not in request.files:
            pass
        else:
            file = request.files['uploaded_file']
            if file.filename != '':
                filename = secure_filename(file.filename)
            else:
                filename = "default"

            file_path = os.path.join('static/files', filename)
            file.save(file_path)

        student_id = flask.request.cookies.get("userid")
        assignment_id = flask.request.form.get("assignment_id")
        print(assignment_id)
    try:
            cursor.execute("INSERT INTO Submissions (student_id, assignment_id, file_path) VALUES (%s, %s, %s)", (student_id, assignment_id, file_path))
            sql_cnx.commit()
            return jsonify({'status': 'success', 'message': 'Success!'})
    except Exception as e:
            sql_cnx.rollback()
            return jsonify({'status': 'error', 'message': f'error:{e} '})


@app.route('/course/view/student', methods=['GET'])
def view_course_student():
    if not flask.request.cookies.get("userid"):
        return flask.redirect("/login")
    else:
        if flask.request.method == 'GET':
            id = flask.request.args['course_id']
            cursor.execute("SELECT * FROM Courses WHERE course_id=%s", (id,))
            courses = cursor.fetchone()
            print(courses)




            return render_template('window.html', edit=False, name=session['name'], courses=courses, mode="student course", title="Course View")


@app.route('/course/view/student/enroll', methods=['GET'])
def view_course_student_enroll():
    if not flask.request.cookies.get("userid"):
        return flask.redirect("/login")
    else:
        if flask.request.method == 'GET':
            id = flask.request.args['course_id']
            cursor.execute("SELECT * FROM Courses WHERE course_id=%s", (id,))
            courses = cursor.fetchone()
            print(courses)



            return render_template('window.html', edit=False, name=session['name'], courses=courses, mode="student course enroll", title="Course View")





@app.route('/submission/student/view', methods=['GET', 'POST'])
def view_submission_comment():
    if not flask.request.cookies.get("userid"):
        return flask.redirect("/login")
    else:
        if flask.request.method == 'GET':
            id = flask.request.args['assignment_id']
            cursor.execute("SELECT * FROM Submissions WHERE submission_id=%s", (id,))
            information = cursor.fetchone()
            print(information)
            return render_template('window.html', edit=False, name=session['name'], information=information, mode="Submission View", title="Feedback")



@app.route('/instructor/submission/view', methods=['GET', 'POST'])
def view_submission_instructor():
        user_id = flask.request.cookies.get("userid")
        input = request.args.get('search_input')
        assignment_id = flask.request.args.get('assignment_id')

        cursor.execute(
                "SELECT * FROM Submissions WHERE assignment_id=%s AND score IS NULL ", (assignment_id,))

        submissions = cursor.fetchall()
        print(submissions)
        return flask.render_template('ins_view_submissions.html', kind='instructor', name=session['name'], submissions=submissions)


@app.route('/submission/mark', methods=['GET', 'POST'])
def mark_submission_comment():
    if not flask.request.cookies.get("userid"):
        return flask.redirect("/login")
    else:
        if flask.request.method == 'GET':
            id = flask.request.args['submission_id']
            print(id)
            cursor.execute("SELECT * FROM Submissions WHERE submission_id=%s", (id,))
            information = cursor.fetchone()
            print(information)
            return render_template('edit_window.html', edit=False, name=session['name'], information=information, mode="Create Mode Mark", title="Feedback")
        else:
            submission_id = flask.request.form.get('submission_id')
            assignment_id = flask.request.form.get("assignment_id")
            score = flask.request.form.get("score")
            comment = flask.request.form.get('comments')
            cursor.execute("UPDATE submissions SET score= %s, comment=%s WHERE submission_id=%s AND assignment_id=%s", (score, comment, submission_id, assignment_id))
            sql_cnx.commit()
            return jsonify({'status': 'success', 'message': 'Success!'})




if __name__ == '__main__':
    app.run(debug=True, port=3000)

